From f490e0c7f5c0b111e001c9dd8cf963563a3b0261 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 12 Aug 2026 17:17:10 +0000 Subject: [PATCH] Better explain why COMM_MEMBS might be incorporated into SIGHTINGS It would be even better to rename SIGHTINGS to MEMBERS, and have it show community membership. The build_sightings() function would be renamed build_members(). And SIGHTINGS would then be a view of MEMBERS, excluding the rows that exist in MEMBERS only because of COMM_MEMBS. But this probably also requires that build_members() always look at COMM_MEMBS, last. And have some sort of special Step value. Otherwise we wouldn't know what MEMBERS.Step value to exclude when constructing the SIGHTINGS view. All this is, at present, not the best use of our available time. --- doc/src/analyzed/sightings.m4 | 52 ++++++++++++++++++++++++-- doc/src/functions/build_sightings.m4 | 10 ++++- doc/src/functions/sighting_controls.m4 | 51 ++++++++++++++++++------- 3 files changed, 95 insertions(+), 18 deletions(-) diff --git a/doc/src/analyzed/sightings.m4 b/doc/src/analyzed/sightings.m4 index bf915b6..e6c7695 100644 --- a/doc/src/analyzed/sightings.m4 +++ b/doc/src/analyzed/sightings.m4 @@ -35,8 +35,9 @@ SIGHTINGS |SIGHTINGS_summary| -The SIGHTINGS table is automatically constructed by the system and -cannot be manually maintained. +The SIGHTINGS table is constructed by the system whenever the +|function_build_sightings| function is executed and cannot be manually +maintained. The table's rows are computed from the SokweDB tables' content. Manual adjustments to the SIGHTINGS table can be made by adjusting @@ -88,10 +89,44 @@ The following query reports sightings and their sources\ [#f1]_: ON (sighting_controls.step = sightings.step) ORDER BY sightings.animid, sightings.date; - The combination of |SIGHTINGS.Date| and |SIGHTINGS.AnimID| must be unique. +.. _tracking_daily_community_membership: + +Tracking Daily Community Membership +``````````````````````````````````` + +The |COMM_MEMBS| table tracks daily community membership. +But it is somewhat hard to use. +Because it does not contain a row for every chimpanzee for every day, +querying |COMM_MEMBS| generally involves using inequality operators to +compare both the |COMM_MEMBS|\. |COMM_MEMBS.StartDate| and the +|COMM_MEMBS|.\ |COMM_MEMBS.EndDate| to a date value from some other +table. +For example:: + + WHERE comm_membs.startdate <= pantgrunts_view.date + AND pantgrunts_view.date <= comm_membs.enddate + AND comm_membs.animid = pantgrunts_view.actor + +This can be tedious, if not error prone. + +The system can be configured, via |SIGHTING_CONTROLS| so that +SIGHTINGS has a row for every chimpanzee for every day they exist. +This incorporates the information in |COMM_MEMBS| into SIGHTINGS. +Rows that exist in SIGHTINGS only because of |COMM_MEMBS| can then be +excluded on a per-query basis.\ [#f2]_ + +Such a configuration provides a way to query both the days an +individual was actually observed, the primary function of SIGHTINGS, +and provides an easier way to query community membership on a daily +basis. +The above query fragment would then be simplified to:: + + WHERE pantgrunts_view.date = sightings.date + AND comm_membs.animid = pantgrunts_view.actor + .. contents:: :depth: 2 @@ -169,3 +204,14 @@ determined. The query uses the shorthand ``tablename.*`` to display all a table's columns. It may be more useful to list only those columns of interest. + +.. [#f2] + You would exclude all the SIGHTINGS rows that exist only because + there is a row in |COMM_MEMBS| by referencing the + |SIGHTING_CONTROLS|.\ |SIGHTING_CONTROLS.Step| value, with a query + containing something like:: + + WHERE sightings.step <> 4 + + Should this be burdensome, a view can be constructed to make the + exclusion of |COMM_MEMBS|-sourced rows automatic. diff --git a/doc/src/functions/build_sightings.m4 b/doc/src/functions/build_sightings.m4 index f741bca..e2daf59 100644 --- a/doc/src/functions/build_sightings.m4 +++ b/doc/src/functions/build_sightings.m4 @@ -94,7 +94,7 @@ how the community value is obtained: ``sdb_comm_membs_sc`` - The |COMM_MEMBS|.\ |COMM_MEMBS.CommID| value is used. + The |COMM_MEMBS|.\ |COMM_MEMBS.CommID| value is used.\ [#f1]_ ``sdb_non_brec_sighting_sources_sc`` @@ -137,3 +137,11 @@ Return Value The function returns the number of rows computed, regardless of how many rows previously existed in |SIGHTINGS|. + + +.. rubric:: Footnotes + +.. [#f1] + For more information on why you would or would not want to examine + the |COMM_MEMBS| table when building |SIGHTINGS|, see the + |SIGHTING_CONTROLS| documentation. diff --git a/doc/src/functions/sighting_controls.m4 b/doc/src/functions/sighting_controls.m4 index 44c6da4..455a521 100644 --- a/doc/src/functions/sighting_controls.m4 +++ b/doc/src/functions/sighting_controls.m4 @@ -97,6 +97,16 @@ The available values are: The |COMM_MEMBS| table is searched. + You would search |COMM_MEMBS| when constructing |SIGHTINGS| if you + wished to use |SIGHTINGS| for dual purposes. + The usual purpose is to discover days when an individual + was sighted. + The additional purpose is simplifying queries that find the + community a chimpanzee was a member of on a given date. + There is more information on this + in the :ref:`SIGHTINGS documentation + ` and below. + The SIGHTING_CONTROLS columns used to control the search are: * |SIGHTING_CONTROLS.StartSource| @@ -138,28 +148,42 @@ omitted columns are |null|):: 1 | sdb_roles_sc | sdb_identity_certain 2 | sdb_swelling_sources_sc | 3 | sdb_non_brec_sighting_sources_sc | - 4 | sdb_comm_membs_sc | -The above SIGHTING_CONTROLS content looks for individuals everywhere -possible, but excludes all recorded events that are not certain. -It excludes all |EVENTS| rows but those with an |EVENTS|.\ -|EVENTS.Certainty| value of ``sdb_identity_certain``. +The above SIGHTING_CONTROLS content excludes all recorded events that +are not certain. +This means, it excludes all those |EVENTS| rows that do not have an +|EVENTS|.\ |EVENTS.Certainty| value of ``sdb_identity_certain``. + +The following |SIGHTING_CONTROLS| table constructs a |SIGHTINGS| table +that serves dual purposes, supporting the querying dates of actual +observation as well as simplifying daily community membership +retrieval (all omitted columns are |null|):: + + step | source | certainty + ------+---------------------------+----------- + 1 | sdb_roles_sc | sdb_identity_certain + 2 | sdb_swelling_sources_sc | + 3 | sdb_non_brec_sighting_sources_sc | + 4 | sdb_comm_membs_sc | Notice that ``sdb_comm_membs_sc`` is last. This makes sense because searching is done in |SIGHTING_CONTROLS.Step| order. + +Putting ``sdb_comm_membs_sc`` last allows |SIGHTINGS| to be used to +query actual sightings, as if |COMM_MEMBS| were not considered in the +construction of |SIGHTINGS|. If ``sdb_comm_membs_sc`` was first it would almost always be where the presence of every individual is discovered, because |COMM_MEMBS| reports individuals present over vast swaths of time. This would result in |SIGHTINGS|.\ |SIGHTINGS.Step| values that reveal -very little detail in the way of when individuals were sighted. - -On the other hand, putting ``sdb_comm_membs_sc`` first does place the -|COMM_MEMBS|.\ |COMM_MEMBS.CommID| value in the |SIGHTINGS|.\ -|SIGHTINGS.CommID| column. -If |COMM_MEMBS|.\ |COMM_MEMBS.CommID| is more accurate than the -community data stored elsewhere in the system this could be of -benefit. +very little detail in the way of when individuals were sighted +and there would be no way to tell if an individual was actually seen +on any given day. +But putting ``sdb_comm_membs_sc`` last means that queries of +|SIGHTINGS| which exclude the last step, step ``4`` in this case, have +a view of the table that shows only those days when individuals were +seen. A SIGHTING_CONTROLS table that provides detail on exactly which behavior the sighted individual was involved with is (all @@ -181,7 +205,6 @@ omitted columns are |null|):: 12 | sdb_roles_sc | sdb_colobus | sdb_identity_certain 13 | sdb_swelling_sources_sc | | 14 | sdb_non_brec_sighting_sources_sc | | - 15 | sdb_comm_membs_sc | | Again, the |SIGHTING_CONTROLS.Step| value is significant. Lower numbers have a higher priority when it comes to what is recorded -- 2.34.1